package cn.doitedu.olap_doit13.dao;

import cn.doitedu.olap_doit13.pojo.ProvinceDau;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

@Repository
public class FlowRptsDaoImpl implements FlowRptsDao {

    @Override
    public List<ProvinceDau> queryProvinceDau(String start, String end) {
        Properties props = new Properties();
        props.setProperty("user","root");
        props.setProperty("SSL","false");

        ArrayList<ProvinceDau> dauLst = new ArrayList<>();
        try {
            Connection conn = DriverManager.getConnection("jdbc:presto://h1:8080/hive/doit13", props);

            Statement stmt = conn.createStatement();

            String sql = "select  \n" +
                    "dt,\n" +
                    "province,\n" +
                    "dau_cnts\n" +
                    "from ads_apl_dau_cube\n" +
                    "where province is not null \n" +
                    "and  city is null \n" +
                    "and  district is null \n" +
                    "and  appver is null \n" +
                    "and devicetype  is null \n" +
                    "and dt>='" + start + "' and dt<='" + end + "'\n" +
                    "\n";

            System.out.println(sql);

            ResultSet rs = stmt.executeQuery(sql);
            /***
             *      dt     |     province     | dau_cnts
             * ------------+------------------+----------
             *  2020-03-12 | 云南省           |        1
             *  2020-03-12 | 北京市           |        1
             *  2020-03-12 | 四川省           |        1
             */
            while (rs.next()) {
                String dt = rs.getString(1);
                String province = rs.getString(2);
                int dau = rs.getInt(3);

                // 封装数据
                ProvinceDau provinceDau = new ProvinceDau();
                provinceDau.set(dt, province, dau);
                // 添加到list中
                dauLst.add(provinceDau);
            }

            rs.close();
            stmt.close();
            conn.close();
        }catch(Exception e){

        }
        return dauLst;
    }
}
